package com.qianfeng.missfood.db;

import android.database.Cursor;

import com.lidroid.xutils.DbUtils;
import com.lidroid.xutils.exception.DbException;
import com.qianfeng.missfood.bean.User;

/**
 * Created by Administrator on 15-8-19.
 */
public class DBHelper {

    public static class DbUser {

        public static final String TABNAME = "USER";
        public static final String ID = "_ID";
        public static final String PHONE = "PHONE";
        public static final String PSW = "PSW";
        public static final String NICKNAME = "NICKNAME";
        public static final String SEX = "SEX";

        public static DbUtils dbUtils;

        public static void getDbUtils(DbUtils dbUtils) {

            DbUser.dbUtils = dbUtils;
            DBHelper.DbUser.createTab();

        }

        public static final String CREATE_TAB = "CREATE TABLE IF NOT EXISTS " + TABNAME + " ( "
                + ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + PHONE + " TEXT NOT NULL,"
                + PSW + " TEXT NOT NULL," + NICKNAME + " NVARCHAR(20)," + SEX + " NVARCHAR(4)" + ")";

        public static final String DROP_TAB = "DROP TABLE IF EXISTS" + TABNAME;

        public static boolean createTab() {

            return executeSql(CREATE_TAB);
        }

        public static boolean deleteTab() {

            return executeSql(DROP_TAB);
        }

        public static boolean selectByPhone(String phone) {

            Cursor cursor;
            try {
                cursor = dbUtils.execQuery("SELECT * FROM " + TABNAME + " WHERE " + PHONE + " = '" + phone + "'");
            } catch (DbException e) {

                return false;
            }

            if (!cursor.moveToNext()) {

                return false;
            }

            return true;
        }

        public static boolean selectUser(User user) {

            Cursor cursor;

            try {
                cursor = dbUtils.execQuery("SELECT * FROM " + TABNAME + " WHERE " + PHONE + " = '" +
                        user.getPhone() + "' AND " + PSW + " = '" + user.getPsw() + "'");

            } catch (DbException e) {

                return false;
            }

            if (cursor.moveToFirst()) {

                String nickName = cursor.getString(cursor.getColumnIndex(NICKNAME));
                String sex = cursor.getString(cursor.getColumnIndex(SEX));
                user.setNickName(nickName + "");
                user.setSex(sex + "");

            }else{

                return false;
            }

            return true;
        }

        public static boolean addUser(User user) {

            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO ");
            sb.append(TABNAME);
            sb.append(" (");
            sb.append(PHONE);
            sb.append(", ");
            sb.append(PSW);
            sb.append(", ");
            sb.append(NICKNAME);
            sb.append(", ");
            sb.append(SEX);
            sb.append(") ");
            sb.append("VALUES ('");
            sb.append(user.getPhone());
            sb.append("', '");
            sb.append(user.getPsw());
            sb.append("', '");
            sb.append(user.getNickName());
            sb.append("', '");
            sb.append(user.getSex());
            sb.append("')");

            return executeSql(sb.toString());
        }

        public static boolean deleteUser(User user) {

            return executeSql("delete from " + TABNAME + " where " + PHONE + " = '" + user.getPhone() + "'");
        }

        public static boolean updateUser(User user) {

            return executeSql("update " + TABNAME + " set " + PSW + " = '" + user.getPsw() + "' where " + PHONE + " = '" + user.getPhone() + "'");
        }

        public static boolean addColumn(String col, String type) {

            return executeSql("ALTER TABLE " + TABNAME + " ADD COLUMN " + col + " " + type);
        }

        public static boolean executeSql(String sql) {

            try {
                dbUtils.execNonQuery(sql);

            } catch (DbException e) {

                return false;
            }

            return true;
        }

    }

}
